/**
 * PlanDAO.java
 * Nov 15, 2013
 * Copyright: phongtdse60732@fpt.edu.vn
 */
package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import XMLEntities.DishListXM;
import XMLEntities.DishXM;
import XMLEntities.PlanInMealListXM;
import XMLEntities.PlanInMealXM;

import DBUltilities.ConnectDB;
import DTO.DishDTO;
import DTO.MenuDTO;
import DTO.PlanDTO;

/**
 * @author Lubu
 * @version 1.0
 */
public class PlanDAO {
	final int PRELOAD_DAYS = 3;
	
	public PlanInMealListXM convertPlansToPlanInMeals(ArrayList<PlanDTO> planList) {
		PlanInMealListXM planInMealListXM = new PlanInMealListXM();
		DishDAO dishDAO = new DishDAO();
		boolean found = false;
		
		for (PlanDTO plan : planList) {
			found = false;
			DishDTO dishDTO = plan.getDish();
			DishXM dishXM = new DishXM();
			for (PlanInMealXM planInMeal : planInMealListXM.getPlanInMealList()) {
				if ((planInMeal.getMenuId() == plan.getMenu().getId()) && (planInMeal.getMealOrder() == plan.getMealOrder())) {
					found = true;
					
					dishDAO.get(dishDTO);
					dishXM = dishDAO.convertDishtoDishXM(dishDTO);
					planInMeal.getDishList().getDishList().add(dishXM);
				}
			}
			if (!found) {
				PlanInMealXM planInMealXM = new PlanInMealXM(plan.getMenu().getId(), plan.getMealOrder(), new DishListXM());
				dishDAO.get(dishDTO);
				dishXM = dishDAO.convertDishtoDishXM(dishDTO);
				planInMealXM.getDishList().getDishList().add(dishXM);
				
				planInMealListXM.getPlanInMealList().add(planInMealXM);
			}
		}
		return planInMealListXM;
	}
	
	public ArrayList<PlanDTO> select() {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ArrayList<PlanDTO> result = new ArrayList<PlanDTO>();

		String sqlStatement = "SELECT * FROM PLAN";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				PlanDTO planDTO = new PlanDTO();
				planDTO.getDish().setId(
						Integer.parseInt(rs.getObject(1) == null ? "0" : rs
								.getObject(1).toString()));
				planDTO.getMenu().setId(
						Integer.parseInt(rs.getObject(2) == null ? "0" : rs
								.getObject(2).toString()));
				planDTO.setMealOrder(Integer
						.parseInt(rs.getObject(3) == null ? "0" : rs
								.getObject(3).toString()));
				result.add(planDTO);
			}
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		return result;
	}
	
	public ArrayList<PlanDTO> getByMenuMeal(int menuId, int mealOrder) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ArrayList<PlanDTO> result = new ArrayList<PlanDTO>();

		String sqlStatement = "SELECT * FROM PLAN WHERE MENUID = ? AND MEALORDER = ?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);
			preparedStatement.setInt(1, menuId);
			preparedStatement.setInt(2, mealOrder);
			ResultSet rs = preparedStatement.executeQuery();

			while (rs.next()) {
				PlanDTO planDTO = new PlanDTO();
				planDTO.getDish().setId(
						Integer.parseInt(rs.getObject(1) == null ? "0" : rs
								.getObject(1).toString()));
				planDTO.getMenu().setId(
						Integer.parseInt(rs.getObject(2) == null ? "0" : rs
								.getObject(2).toString()));
				planDTO.setMealOrder(Integer
						.parseInt(rs.getObject(3) == null ? "0" : rs
								.getObject(3).toString()));
				result.add(planDTO);
			}
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		return result;
	}
	
	public ArrayList<PlanDTO> getByMenu(int menuid) {
		if (menuid != 0) {
			Connection conn = null;
			PreparedStatement preparedStatement = null;
			ArrayList<PlanDTO> result = new ArrayList<PlanDTO>();

			String sqlStatement = "SELECT * FROM PLAN WHERE MENUID = ? ";

			try {
				conn = ConnectDB.getConnection();
				preparedStatement = conn.prepareStatement(sqlStatement);
				preparedStatement.setInt(1, menuid);
				ResultSet rs = preparedStatement.executeQuery();

				PlanDTO planDTO = new PlanDTO();
				while (rs.next()) {
					planDTO.getDish().setId(
							Integer.parseInt(rs.getObject(1) == null ? "0" : rs
									.getObject(1).toString()));
					planDTO.getMenu().setId(
							Integer.parseInt(rs.getObject(2) == null ? "0" : rs
									.getObject(2).toString()));
					planDTO.setMealOrder(Integer
							.parseInt(rs.getObject(3) == null ? "0" : rs
									.getObject(3).toString()));
					result.add(planDTO);
				}
			} catch (SQLException e) {
			} finally {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (SQLException e) {
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
			}
			return result;
		}
		return null;
	}
	
	public ArrayList<PlanDTO> getByDay(int menuid, int fromDay, int toDay) {
		if (menuid != 0) {
			MenuDAO menuDAO = new MenuDAO();
			MenuDTO menuDTO = new MenuDTO();
			menuDTO.setId(menuid);
			menuDAO.get(menuDTO);
			
			Connection conn = null;
			PreparedStatement preparedStatement = null;
			ArrayList<PlanDTO> result = new ArrayList<PlanDTO>();

			String sqlStatement = "SELECT * FROM PLAN WHERE MENUID = ? AND MEALORDER BETWEEN ? AND ?";

			try {
				conn = ConnectDB.getConnection();
				preparedStatement = conn.prepareStatement(sqlStatement);
				
				preparedStatement.setInt(1, menuid);
				preparedStatement.setInt(2, (fromDay - 1) * menuDTO.getNumbermeal() + 1);
				preparedStatement.setInt(3, toDay * menuDTO.getNumbermeal());
				ResultSet rs = preparedStatement.executeQuery();

				while (rs.next()) {
					PlanDTO planDTO = new PlanDTO();
					planDTO.getDish().setId(Integer.parseInt(rs.getObject(1) == null ? "0" : rs.getObject(1).toString()));
					planDTO.setMenu(menuDTO);
					planDTO.setMealOrder(Integer.parseInt(rs.getObject(3) == null ? "0" : rs.getObject(3).toString()));
					result.add(planDTO);
				}
			} catch (SQLException e) {
			} finally {
				if (preparedStatement != null) {
					try {
						preparedStatement.close();
					} catch (SQLException e) {
					}
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
				}
			}
			return result;
		}
		return null;
	}

	public void add(PlanDTO planDTO) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "INSERT INTO PLAN (DISHID, MENUID, MEALORDER) VALUES (?,?,?)";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, planDTO.getDish().getId());
			preparedStatement.setInt(2, planDTO.getMenu().getId());
			preparedStatement.setInt(3, planDTO.getMealOrder());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void update(int menuId, int mealOrder, int[] dishIdList) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		// delete all Dish in a Meal of a Menu
		String sqlStatement = "DELETE FROM PLAN WHERE MENUID=?, MEALORDER=?";
		
		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, menuId);
			preparedStatement.setInt(2, mealOrder);
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
		
		// insert new Dishes to Meal of a Menu
		sqlStatement = "INSERT INTO PLAN (DISHID, MENUID, MEALORDER) VALUES (?,?,?)";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			for (int dishId : dishIdList) {
				preparedStatement.setInt(1, dishId);
				preparedStatement.setInt(2, menuId);
				preparedStatement.setInt(3, mealOrder);

				preparedStatement.executeUpdate();
			}
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}
	
	public void deleteByMeal(int menuId, int mealOrder) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM PLAN WHERE MENUID=? AND MEALORDER=?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, menuId);
			preparedStatement.setInt(2, mealOrder);

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

	public void delete(PlanDTO planDTO) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;

		String sqlStatement = "DELETE FROM PLAN WHERE DISHID=? AND MENUID=? AND MEALORDER=?";

		try {
			conn = ConnectDB.getConnection();
			preparedStatement = conn.prepareStatement(sqlStatement);

			preparedStatement.setInt(1, planDTO.getDish().getId());
			preparedStatement.setInt(2, planDTO.getMenu().getId());
			preparedStatement.setInt(3, planDTO.getMealOrder());

			preparedStatement.executeUpdate();
		} catch (SQLException e) {
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}
	}

}
